const { Service } = require('egg');
const { v4: uuidV4 } = require('uuid');
const moment = require('moment');
const product_table = 'product',
  user_table = 'user',
  price_table = 'price';
class ProductService extends Service {


  // 获取价格管理列表
  async list(product_id, remark, type, is_expires, order, method, pageIndex, pageSize) {
    const ctx = this.ctx;
    try {
      let sql = ctx.app.knex().select('t1.uuid', 't1.price', 't1.remark', 't1.type',
        't1.end_time', 't1.create_time', 't1.update_time', 't1.create_by', 't1.update_by',
        't3.name as create_name', 't4.name as update_name', 't2.name as product_name'
      )
        .from(`${price_table} as t1`)
        .leftJoin(`${product_table} as t2`, function() {
          this.on('t1.product_id', '=', 't2.uuid');
        })
        .leftJoin(`${user_table} as t3`, function() {
          this.on('t1.create_by', '=', 't3.uuid');
        })
        .leftJoin(`${user_table} as t4`, function() {
          this.on('t1.update_by', '=', 't4.uuid');
        })
        .whereRaw('t1.is_delete = 0');

      let countSql = ctx.app.knex(price_table).count('uuid as count');
      if (product_id) {
        sql = sql.whereRaw(`t1.product_id = '${product_id}'`);
        countSql = countSql.where({ product_id });
      }

      if (remark) {
        sql = sql.andWhereLike('t1.remark', `%${remark}%`);
        countSql = countSql.andWhereLike('remark', `%${remark}%`);
      }

      if (type) {
        type += '_';
        sql = sql.andWhereLike('t1.type', `${type}%`);
        countSql = countSql.andWhereLike('type', `${type}%`);
      }

      if (is_expires) {
        const time = moment().format('YYYY-MM-DD HH:mm:ss');
        if (Number(is_expires) === 0) {
          sql = sql.andWhere(`t1.end_time > '${time}'`);
          countSql = countSql.andWhere(`end_time > '${time}'`);
        } else {
          sql = sql.andWhere(`t1.end_time < '${time}'`);
          countSql = countSql.andWhere(`end_time <' ${time}'`);
        }
      }

      sql = sql.orderBy(order, method).limit(Number(pageSize)).offset((Number(pageIndex) - 1) * Number(pageSize));
      const list = await sql;
      const count = await countSql;

      return {
        list,
        count: count[0].count,
      };
    } catch (error) {
      throw error;
    }
  }

  // 新增商品价格
  async insert(product_id, price, remark, type, end_time) {
    const ctx = this.ctx;
    try {
      const time = moment();
      const { id: user_id } = ctx.session;
      const instance = {
        uuid: uuidV4(),
        product_id, price, remark, type,
        end_time: moment(end_time).format('YYYY-MM-DD HH:mm:ss'),
        create_by: user_id,
        update_by: user_id,
        create_time: moment(time).format('YYYY-MM-DD HH:mm:ss'),
        update_time: moment(time).format('YYYY-MM-DD HH:mm:ss'),
      };

      await ctx.app.knex(price_table).insert(instance);

      return instance;
    } catch (error) {
      throw error;
    }
  }

  async update(remark, uuid) {
    const ctx = this.ctx;
    try {
      const time = moment();
      const { id: user_id } = ctx.session;

      const instance = await ctx.app.knex().select('*').from(price_table)
        .where({ uuid, is_delete: 0 })
        .first();

      if (!instance) {
        throw new Error('当前价格不存在');
      }

      instance.update_time = moment(time).format('YYYY-MM-DD HH:mm:ss');
      instance.update_by = user_id;
      instance.remark = remark;

      await ctx.app.knex(price_table).update(instance).where({ uuid });

      return instance;
    } catch (error) {
      throw error;
    }
  }

  async delete(ids) {
    const ctx = this.ctx;
    try {
      const time = moment();
      const { id: user_id } = ctx.session;
      const instance = {
        update_by: user_id,
        update_time: moment(time).format('YYYY-MM-DD HH:mm:ss'),
        is_delete: 1,
      };
      await ctx.app.knex(price_table).update(instance).whereIn('uuid', ids);

      return;
    } catch (error) {
      throw error;
    }
  }
}

module.exports = ProductService;
